package org.ezplatform.office.meeting.statistics.service;

import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Comparator;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;

import javax.servlet.http.HttpServletRequest;

import org.apache.commons.collections.CollectionUtils;
import org.ezplatform.cloud.api.system.org.OrgApiService;
import org.ezplatform.component.popuser.service.CmpPopUserOrgName;
import org.ezplatform.core.common.WebUser;
import org.ezplatform.core.dao.jpa.JpaBaseDao;
import org.ezplatform.core.service.BaseService;
import org.ezplatform.core.web.util.WebUtils;
import org.ezplatform.office.meeting.common.util.MeetingUtils;
import org.ezplatform.office.meeting.info.entity.ApplyRecord;
import org.ezplatform.office.meeting.statistics.entity.AttendMeetingStatisticsExport;
import org.ezplatform.office.meeting.statistics.entity.TaskStatisticsExport;
import org.ezplatform.util.DateUtils;
import org.ezplatform.util.JsonUtils;
import org.ezplatform.util.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageImpl;
import org.springframework.data.domain.Pageable;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;

@Controller
@RequestMapping(value = "/meetingStatisticsService")
public class MeetingStatisticsService extends BaseService<ApplyRecord, String>{
	private final static Logger logger = LoggerFactory.getLogger(MeetingStatisticsService.class);

	@Autowired
    private OrgApiService orgApiService;
	
	@Autowired
    private CmpPopUserOrgName cmpPopUserOrgName;
	
	@Override
    protected JpaBaseDao<ApplyRecord, String> getEntityDao() {
        return null;
    }
	
	/**
	 * 获取参会统计列表数据(分页)<br/>
	 * 模糊查询使用
	 * @param pageable
	 * @param corpId
	 * @param name
	 * @param org
	 * @param startTime
	 * @param endTime
	 * @return
	 */
	public Page<Map<String, Object>> getAttendMeetingStatisticsPage(Pageable pageable, String corpId, String name, String orgId, 
			String startTime, String endTime) {
		//查询所有参会人
		Map<String, Object> paramMap = new HashMap<String, Object>();
		StringBuffer sql = new StringBuffer();
		sql.append("select ni.notice_user_id id,u.user_name,o.org_name from oa_meeting_notice_info ni");
		sql.append(" inner join oa_meeting_apply_record ar on ni.apply_record_id=ar.id");
		sql.append("	and (ar.status=1 or ar.status=4 or ar.status=5) and ar.is_sync=1 and ni.corp_id=:corpId and ni.status!=4 ");
		if(StringUtils.isNotBlank(startTime)) {
			sql.append(" and ar.start_time >= :startTime");
			paramMap.put("startTime", startTime + " 00:00");
		}
		if(StringUtils.isNotBlank(endTime)) {
			sql.append(" and ar.start_time <= :endTime");
			paramMap.put("endTime", endTime + "23:59");
		}
		sql.append(" inner join sys_user u on ni.notice_user_id=u.id ");
		if(StringUtils.isNotBlank(name)) {
			sql.append(" and u.user_name like :name");
			paramMap.put("name", "%"+ name +"%");
		}
		sql.append(" left join sys_org_user ou on u.id=ou.user_id and ou.isMain='1'");
		sql.append(" left join sys_org o on ou.org_id=o.id and o.is_delete=0");
		sql.append(" where (1=1) ");
		
		if(StringUtils.isNotBlank(orgId)) {
			sql.append(" and ou.org_id=:orgId");
			paramMap.put("orgId", orgId);
		}
		
		sql.append(" group by ni.notice_user_id,u.user_name,o.org_name order by ni.notice_user_id asc");
		paramMap.put("corpId", corpId);
		logger.debug("-----sql: "+sql.toString());
		long time=System.currentTimeMillis();
		Page<Map> page = super.findByPageNativeSQL(pageable, sql.toString(), paramMap);
		logger.debug("------zhaozy1:"+(System.currentTimeMillis()-time));
		time=System.currentTimeMillis();
		List<Map> allList = page.getContent();
		List<String> userIdList = new ArrayList<String>();
		if(CollectionUtils.isNotEmpty(allList)) {
			for(Map m : allList) {
				userIdList.add(StringUtils.null2String(m.get("id")));
			}
		}
		if(CollectionUtils.isEmpty(userIdList)) return new PageImpl<Map<String, Object>>(new ArrayList<Map<String, Object>>(), pageable, 0);
		logger.debug("------zhaozy2:"+(System.currentTimeMillis()-time));
		time=System.currentTimeMillis();
		Map<String, String> attendNumList = queryAttendMeetingStatistics(userIdList, 1, startTime, endTime);
		logger.debug("------zhaozy3:"+(System.currentTimeMillis()-time));
		time=System.currentTimeMillis();
		Map<String, String> onTimeList = queryAttendMeetingStatistics(userIdList, 2, startTime, endTime);
		logger.debug("------zhaozy4:"+(System.currentTimeMillis()-time));
		time=System.currentTimeMillis();
		Map<String, String> beLateList = queryAttendMeetingStatistics(userIdList, 3, startTime, endTime);
		logger.debug("------zhaozy5:"+(System.currentTimeMillis()-time));
		time=System.currentTimeMillis();
		Map<String, String> leaveList = queryAttendMeetingStatistics(userIdList, 4, startTime, endTime);
		logger.debug("------zhaozy6:"+(System.currentTimeMillis()-time));
		time=System.currentTimeMillis();
		Map<String, String> absentList = queryAttendMeetingStatistics(userIdList, 5, startTime, endTime);
		logger.debug("------zhaozy7:"+(System.currentTimeMillis()-time));
		time=System.currentTimeMillis();
		List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
		if(CollectionUtils.isNotEmpty(allList)) {
			for(Map m : allList) {
				Map<String, Object> element = new HashMap<String, Object>();
				element.put("userName", m.get("user_name"));
				element.put("org", m.get("org_name"));
				String key = StringUtils.null2String(m.get("id"));
				int attendNum = 0, onTime = 0, beLate = 0, leave = 0, absent = 0;
				if(attendNumList.get(key) != null) attendNum = Integer.parseInt(attendNumList.get(key));
				if(onTimeList.get(key) != null) onTime = Integer.parseInt(onTimeList.get(key));
				if(beLateList.get(key) != null) beLate = Integer.parseInt(beLateList.get(key));
				if(leaveList.get(key) != null) leave = Integer.parseInt(leaveList.get(key));
				if(absentList.get(key) != null) absent = Integer.parseInt(absentList.get(key));
				element.put("attendNum", attendNum);
				element.put("onTime", onTime + "(" + getPercent(onTime, attendNum) + "%)");
				element.put("beLate", beLate + "(" + getPercent(beLate, attendNum) + "%)");
				element.put("leave", leave + "(" + getPercent(leave, attendNum) + "%)");
				element.put("absent", absent + "(" + getPercent(absent, attendNum) + "%)");
				list.add(element);
			}
			//List<Map<String, Object>>集合按Map的key排序
//			Collections.sort(list, new Comparator<Map<String, Object>>() {
//				public int compare(Map<String, Object> o1, Map<String, Object> o2) {
//					String map1value = (String) o1.get("userName");
//					String map2value = (String) o2.get("userName");
//					return map2value.compareTo(map1value);
//				}
//			});
			logger.debug("------zhaozy8:"+(System.currentTimeMillis()-time));
			time=System.currentTimeMillis();
		}
		return new PageImpl<Map<String, Object>>(list, pageable, page.getTotalElements());
	}
	
	/**
	 * 查询相关参会次数
	 * @param userIdList
	 * @param type 1:参会次数,2:准时参会,3:迟到,4:请假,5:缺席
	 * @return
	 */
	private Map<String, String> queryAttendMeetingStatistics(List<String> userIdList, int type, String startTime, String endTime){
		Map<String, Object> paramMap = new HashMap<String, Object>();
		StringBuffer sql = new StringBuffer();
		if(type == 1) {// 参会次数
			sql.append("select ni.notice_user_id userId,count(ar.id) num from (");
			sql.append(" select ni.notice_user_id,ni.apply_record_id from oa_meeting_notice_info ni where ni.status != 4 and");
			sql.append(MeetingUtils.getWhereInValuesSql("ni.notice_user_id", userIdList, 1000));
			sql.append(" )ni join oa_meeting_apply_record ar");
			sql.append(" on ni.apply_record_id=ar.id and (ar.status=1 or ar.status=4 or ar.status=5)");
			if(StringUtils.isNotBlank(startTime)) {
				sql.append(" and ar.start_time >= :startTime");
				paramMap.put("startTime", startTime + " 00:00");
			}
			if(StringUtils.isNotBlank(endTime)) {
				sql.append(" and ar.start_time <= :endTime");
				paramMap.put("endTime", endTime + "23:59");
			}
			sql.append(" group by ni.notice_user_id");
		}else if(type == 2) {// 准时参会
			sql.append("select t.userId userId,count(t.id) num from (");
			sql.append("	select ni.notice_user_id userId,ar.id,ar.start_time from (select ni.notice_user_id,ni.apply_record_id from oa_meeting_notice_info ni where ");
			sql.append("    ni.status != 4 and ni.status != 3 and ");
			sql.append(MeetingUtils.getWhereInValuesSql("ni.notice_user_id", userIdList, 1000));
			sql.append(" 	)ni join oa_meeting_apply_record ar ");
			sql.append("	on ni.apply_record_id=ar.id  and ar.status=5 and ar.is_signed=0");
			sql.append("	union");
			sql.append("	select si.signed_user_id userId,ar.id,ar.start_time from( ");
			sql.append(" 	select si.signed_user_id,si.apply_record_id, si.is_signed,si.position_time,si.qrcode_time from oa_meeting_signed_info si where  si.is_signed is not null and ");
			sql.append(MeetingUtils.getWhereInValuesSql("si.signed_user_id", userIdList, 1000));
			sql.append(" 	)si join oa_meeting_apply_record ar ");
			sql.append("	on ar.is_signed=1 and ar.id=si.apply_record_id and (ar.status=1 or ar.status=4 or ar.status=5) ");
			sql.append("	and ((ar.signed_type='1' and fn_date_format(si.position_time,'yyyy-MM-dd HH:mm') < ar.start_time) or (ar.signed_type='0' and fn_date_format(si.qrcode_time,'yyyy-MM-dd HH:mm') < ar.start_time)or");
			sql.append("	(ar.signed_type='2' and fn_date_format(si.qrcode_time,'yyyy-MM-dd HH:mm') < ar.start_time and fn_date_format(si.position_time,'yyyy-MM-dd HH:mm') < ar.start_time))) t");
			sql.append(" where (1=1)");
			
			if(StringUtils.isNotBlank(startTime)) {
				sql.append(" and t.start_time >= :startTime");
				paramMap.put("startTime", startTime + " 00:00");
			}
			if(StringUtils.isNotBlank(endTime)) {
				sql.append(" and t.start_time <= :endTime");
				paramMap.put("endTime", endTime + "23:59");
			}
			sql.append(" group by t.userId");
		}else if(type == 3) {// 迟到
			sql.append("select si.signed_user_id userId,count(ar.id) num from (select si.signed_user_id,si.apply_record_id,si.qrcode_time,si.position_time from oa_meeting_signed_info si where ");
			sql.append(MeetingUtils.getWhereInValuesSql("si.signed_user_id", userIdList, 1000));
			sql.append(" ) si join oa_meeting_apply_record ar ");
			sql.append(" on ar.is_signed=1 and ar.id=si.apply_record_id and (ar.status=1 or ar.status=4 or ar.status=5)");
			sql.append(" and ((ar.signed_type='0' and  fn_date_format(si.qrcode_time,'yyyy-MM-dd HH:mm') > ar.start_time and fn_date_format(si.qrcode_time,'yyyy-MM-dd HH:mm') <= ar.end_time ) ");
			sql.append(" or (ar.signed_type='1' and  fn_date_format(si.position_time,'yyyy-MM-dd HH:mm') > ar.start_time and fn_date_format(si.position_time,'yyyy-MM-dd HH:mm') <= ar.end_time )");
			sql.append(" or (ar.signed_type='2' and si.qrcode_time is not null and si.position_time is not null ");
			sql.append(" and ((fn_date_format(si.qrcode_time,'yyyy-MM-dd HH:mm') > ar.start_time AND fn_date_format(si.qrcode_time,'yyyy-MM-dd HH:mm') <= ar.end_time) or (fn_date_format(si.position_time,'yyyy-MM-dd HH:mm') > ar.start_time and fn_date_format(si.position_time,'yyyy-MM-dd HH:mm') <= ar.end_time))))");
			
			if(StringUtils.isNotBlank(startTime)) {
				sql.append(" and ar.start_time >= :startTime");
				paramMap.put("startTime", startTime + " 00:00");
			}
			if(StringUtils.isNotBlank(endTime)) {
				sql.append(" and ar.start_time <= :endTime");
				paramMap.put("endTime", endTime + "23:59");
			}
			sql.append(" group by si.signed_user_id");
		}else if(type == 4) {// 请假
			sql.append("select ni.notice_user_id userId,count(ar.id) num from oa_meeting_notice_info ni join oa_meeting_apply_record ar");
			sql.append(" on ni.apply_record_id=ar.id and (ar.status=1 or ar.status=4 or ar.status=5) and ni.status=3 and ");
			sql.append(MeetingUtils.getWhereInValuesSql("ni.notice_user_id", userIdList, 1000));
			if(StringUtils.isNotBlank(startTime)) {
				sql.append(" and ar.start_time >= :startTime");
				paramMap.put("startTime", startTime + " 00:00");
			}
			if(StringUtils.isNotBlank(endTime)) {
				sql.append(" and ar.start_time <= :endTime");
				paramMap.put("endTime", endTime + "23:59");
			}
			sql.append(" where not exists(select 1 from oa_meeting_signed_info si where si.apply_record_id=ni.apply_record_id and si.signed_user_id=ni.notice_user_id and si.is_signed is not null)");
			sql.append(" group by ni.notice_user_id");
		}else if(type == 5) {// 缺席
			sql.append("select ni.notice_user_id userId, COUNT(ar.id) num");
			sql.append(" from oa_meeting_notice_info ni");
			sql.append(" inner join oa_meeting_apply_record ar on ni.status != 4 and ni.status != 3 and ar.id=ni.apply_record_id ");
			sql.append("	and ar.status=5 and ar.is_signed=1 and ar.end_time < :now and ");
			if(StringUtils.isNotBlank(startTime)) {
				sql.append(" and ar.start_time >= :startTime");
				paramMap.put("startTime", startTime + " 00:00");
			}
			if(StringUtils.isNotBlank(endTime)) {
				sql.append(" and ar.start_time <= :endTime");
				paramMap.put("endTime", endTime + "23:59");
			}
			sql.append(MeetingUtils.getWhereInValuesSql("ni.notice_user_id", userIdList, 1000));
			sql.append(" inner join oa_meeting_signed_info si on si.is_signed is null and ni.apply_record_id=si.apply_record_id	and ni.notice_user_id=si.signed_user_id");
			sql.append(" group by ni.notice_user_id");
			String searchTime = DateUtils.formatDate(new Date(), "yyyy-MM-dd HH:mm");
			paramMap.put("now", searchTime);
		}
//		paramMap.put("userIds", userIdList);
		List<Map> list = super.findByListNativeQuery(sql.toString(), null, paramMap);
		Map<String, String> map = new HashMap<String, String>();
		if(CollectionUtils.isNotEmpty(list)) {
			for(Map m : list) {
				map.put(StringUtils.null2String(m.get("userId")), StringUtils.null2String(m.get("num")));
			}
		}
		return map;
	}
	
	/**
	 * 获取任务统计列表数据(分页)
	 * @param pageable
	 * @param corpId
	 * @param name
	 * @param org
	 * @param startTime
	 * @param endTime
	 * @return
	 */
	public Page<Map<String, Object>> getTaskStatisticsPage(Pageable pageable, String corpId, String name, String orgId, 
			String startTime, String endTime) {
		Map<String, Object> paramMap = new HashMap<String, Object>();
		StringBuffer sql = new StringBuffer();
		sql.append("select u.user_name, o.org_name, ti.createdBy from oa_meeting_task_info ti ");
		sql.append(" join oa_meeting_apply_record_scope s on ti.response_user=s.fieldDataValue and ti.corp_id=:corpId ");
		if(StringUtils.isNotBlank(startTime)) {
			sql.append(" and fn_date_format(ti.end_time,'yyyy-MM-dd') >= :startTime");
			paramMap.put("startTime", startTime);
		}
		if(StringUtils.isNotBlank(endTime)) {
			sql.append(" and fn_date_format(ti.end_time,'yyyy-MM-dd') <= :endTime");
			paramMap.put("endTime", endTime);
		}
		sql.append(" join sys_user u on u.id=s.scopeValue ");
		if(StringUtils.isNotBlank(name)) {
			sql.append(" and u.user_name like :name");
			paramMap.put("name", "%"+ name +"%");
		}
		sql.append(" join sys_org_user ou on ou.user_id=s.scopeValue and ou.isMain=1 ");
		if(StringUtils.isNotBlank(orgId)) {
			sql.append(" and ou.org_id=:orgId");
			paramMap.put("orgId", orgId);
		}
		sql.append(" join sys_org o on o.id=ou.org_id ");
		sql.append(" group by u.user_name, o.org_name, ti.createdBy order by ti.createdBy");
		paramMap.put("corpId", corpId);
		Page<Map> page = super.findByPageNativeSQL(pageable, sql.toString(), paramMap);
		List<Map> allList = page.getContent();
		Map<String, Map> completedMap = queryTaskStatistics(1, corpId, name, orgId, startTime, endTime);
		Map<String, Map> overdueMap = queryTaskStatistics(2, corpId, name, orgId, startTime, endTime);
		Map<String, Map> unCompletedMap = queryTaskStatistics(3, corpId, name, orgId, startTime, endTime);
		List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
		if(CollectionUtils.isNotEmpty(allList)) {
			for(Map m : allList) {
				Map<String, Object> element = new HashMap<String, Object>();
				element.put("userName", m.get("user_name"));
				element.put("org", m.get("org_name"));
				String key = StringUtils.null2String(m.get("user_name"));
				int completed = 0, overdue = 0, unCompleted = 0;
				if(completedMap.get(key) != null) completed = Integer.parseInt(completedMap.get(key).get("num").toString());
				if(overdueMap.get(key) != null) overdue = Integer.parseInt(overdueMap.get(key).get("num").toString());
				if(unCompletedMap.get(key) != null) unCompleted = Integer.parseInt(unCompletedMap.get(key).get("num").toString());
				int taskNum = completed + overdue + unCompleted;
				DecimalFormat df=new DecimalFormat("0.00");//设置保留位数
				element.put("taskNum", taskNum);
				element.put("completed", completed + "(" + getPercent(completed, taskNum) + "%)");
				element.put("overdue", overdue + "(" + getPercent(overdue, taskNum) + "%)");
				element.put("unCompleted", unCompleted + "(" + getPercent(unCompleted, taskNum) + "%)");
				list.add(element);
			}
			//List<Map<String, Object>>集合按Map的key排序
//			Collections.sort(list, new Comparator<Map<String, Object>>() {
//				public int compare(Map<String, Object> o1, Map<String, Object> o2) {
//					String map1value = (String) o1.get("userName");
//					String map2value = (String) o2.get("userName");
//					return map2value.compareTo(map1value);
//				}
//			});
		}
		return new PageImpl<Map<String, Object>>(list, pageable, page.getTotalElements());
	}
	
	private String getPercent(int num, int total) {
		if(total != 0) {
			DecimalFormat df=new DecimalFormat("0");//设置保留位数
			float f = (float)num / total * 100;
			return df.format(f);
		}else {
			return "0";
		}
	}
	
	/**
	 * 查询统计已完成、未完成、超期任务
	 * @param type（1：已完成，2：超期 ，3：未完成）
	 * @param corpId
	 * @param name
	 * @param org
	 * @return
	 */
	private Map<String, Map> queryTaskStatistics(int type, String corpId, String name, String orgId, 
			String startTime, String endTime){
		Map<String, Object> paramMap = new HashMap<String, Object>();
		StringBuffer sql = new StringBuffer();
		sql.append("select u.user_name name, count(ti.id) num from oa_meeting_task_info ti ");
		sql.append(" left join oa_meeting_apply_record_scope s on ti.response_user=s.fieldDataValue");
		sql.append(" inner join sys_user u on u.id=s.scopeValue");
		sql.append(" left join sys_org_user ou on ou.user_id=s.scopeValue and ou.isMain=1");
		sql.append(" where ti.corp_id=:corpId");
		if(type == 1) {
			sql.append(" and ti.progress=100");
		}else {
			String searchTime = DateUtils.formatDate(new Date(), "yyyy-MM-dd");
			paramMap.put("searchTime", searchTime);
			if(type == 2) sql.append(" and fn_date_format(ti.end_time,'yyyy-MM-dd') < :searchTime and ti.progress<100");
			if(type == 3) sql.append(" and fn_date_format(ti.end_time,'yyyy-MM-dd') >= :searchTime and ti.progress<100");
		}
		if(StringUtils.isNotBlank(startTime)) {
			sql.append(" and fn_date_format(ti.end_time,'yyyy-MM-dd') >= :startTime");
			paramMap.put("startTime", startTime);
		}
		if(StringUtils.isNotBlank(endTime)) {
			sql.append(" and fn_date_format(ti.end_time,'yyyy-MM-dd') <= :endTime");
			paramMap.put("endTime", endTime);
		}
		if(StringUtils.isNotBlank(name)) {
			sql.append(" and u.user_name like :name");
			paramMap.put("name", "%"+ name +"%");
		}
		if(StringUtils.isNotBlank(orgId)) {
			sql.append(" and ou.org_id=:orgId");
			paramMap.put("orgId", orgId);
		}
		sql.append(" group by u.user_name");
		paramMap.put("corpId", corpId);
		List<Map> list = super.findByListNativeQuery(sql.toString(), null, paramMap);
		Map<String, Map> map = new HashMap<String, Map>();
		if(CollectionUtils.isNotEmpty(list)) {
			for(Map m : list) {
				String key = StringUtils.null2String(m.get("name"));
				map.put(key, m);
			}
		}
		return map;
	}
	
	/**
	 * 获取会议统计列表数据(分页)
	 * @param pageable
	 * @param corpId
	 * @param meetingType
	 * @param meetingInitiator
	 * @param startTime
	 * @param endTime
	 * @return
	 */
	public Page<Map<String, Object>> getMeetingStatisticsPage(Pageable pageable, String corpId, String meetingType, 
			String meetingInitiator, String startTime, String endTime) {
		int start = (int)pageable.getOffset();
		int end = start + pageable.getPageSize();
		Map<String, Map> convokeMap = this.queryMeetingStatistics(5, corpId, meetingType, meetingInitiator, startTime, endTime);
		Map<String, Map> cancelMap = this.queryMeetingStatistics(3, corpId, meetingType, meetingInitiator, startTime, endTime);
		Map<String, Map> map = new HashMap<String, Map>();
		map.putAll(convokeMap);
		map.putAll(cancelMap);
		List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
		Set<String> set = map.keySet();
		for(String key : set) {
			Map<String, Object> element = new HashMap<String, Object>();
			Map m = map.get(key);
			element.put("meetingType", m.get("meetingType"));
			element.put("meetingInitiator", m.get("meetingInitiator"));
			Map a = convokeMap.get(key);
			if(a != null) element.put("convokeNum", a.get("num"));
			else element.put("convokeNum", 0);
			Map b = cancelMap.get(key);
			if(b != null) element.put("cancelNum", b.get("num"));
			else element.put("cancelNum", 0);
			list.add(element);
		}
		//List<Map<String, Object>>集合按Map的key排序
		Collections.sort(list, new Comparator<Map<String, Object>>() {
			public int compare(Map<String, Object> o1, Map<String, Object> o2) {
				String map1value = (String) o1.get("meetingType");
				String map2value = (String) o2.get("meetingType");
				return map2value.compareTo(map1value);
			}
		});
		int totalNum = list.size();
		if(end > totalNum) end = totalNum;
		return new PageImpl<Map<String, Object>>(list.subList(start, end), pageable, totalNum);
	}
	
	private Map<String, Map> queryMeetingStatistics(int status, String corpId, String meetingType, String meetingInitiator, 
			String startTime, String endTime){
		Map<String, Object> paramMap = new HashMap<String, Object>();
		StringBuffer sql = new StringBuffer();
		sql.append("select t.type_name meetingType, u.user_name meetingInitiator, count(ar.status) num,t.id tid,u.id ");
		sql.append(" from oa_meeting_type t,oa_meeting_apply_record ar,sys_user u");
		sql.append(" where t.corp_id=:corpId and t.id=ar.meeting_type_id and ar.status=:status and ar.createdBy=u.id");
		if(StringUtils.isNotBlank(startTime)) {
			sql.append(" and ar.start_time >= :startTime");
			paramMap.put("startTime", startTime + " 00:00");
		}
		if(StringUtils.isNotBlank(endTime)) {
			sql.append(" and ar.start_time <= :endTime");
			paramMap.put("endTime", endTime + " 23:59");
		}
		if(StringUtils.isNotBlank(meetingType)) {
			sql.append(" and t.id = :meetingType");
			paramMap.put("meetingType", meetingType);
		}
		if(StringUtils.isNotBlank(meetingInitiator)) {
			sql.append(" and u.user_name like :meetingInitiator");
			paramMap.put("meetingInitiator", "%"+ meetingInitiator +"%");
		}
		sql.append(" group by t.type_name, u.user_name,t.id,u.id");
		sql.append(" order by t.id,u.id");
		paramMap.put("status", status);
		paramMap.put("corpId", corpId);
		List<Map> list = super.findByListNativeQuery(sql.toString(), null, paramMap);
		Map<String, Map> map = new HashMap<String, Map>();
		if(CollectionUtils.isNotEmpty(list)) {
			for(Map m : list) {
				String key1 = StringUtils.null2String(m.get("meetingType"));
				String key2 = StringUtils.null2String(m.get("meetingInitiator"));
				map.put(key1 + key2, m);
			}
		}
		return map;
	}
	
	/**
     * 查询部门树数据
     * 
     * @param request
     * @param corpId
     * @param token 
     * @return
     */
    public String getManageOrgTreeData(String userId, String userName,HttpServletRequest request, String corpId, String token) {
        String orgTreeJsonList = orgApiService.getOrgTree(userId, userName, "", corpId, token);
        Map<String, Object> scopeName = JsonUtils.readValue(orgTreeJsonList);
        return String.valueOf(scopeName.get("data"));
    }
    
    /**
	 * 参会统计导出
	 * @param paramMap
	 * @return
	 */
	public List<AttendMeetingStatisticsExport> getAttendMeetingStatisticsExport(Map<String, String> paramMap){
		List<AttendMeetingStatisticsExport> result = new ArrayList<AttendMeetingStatisticsExport>();
		WebUser user = WebUtils.getCurrentUser();
        String corpId = user.getCorpId();
		String userName = paramMap.get("userName");
		String orgId = paramMap.get("orgName");
		String startTime = paramMap.get("startTime");
		String endTime = paramMap.get("endTime");
		Map<String, Object> param = new HashMap<String, Object>();
		StringBuffer sql = new StringBuffer();
		sql.append("select ni.notice_user_id id,u.user_name,o.org_name from oa_meeting_notice_info ni");
		sql.append(" inner join oa_meeting_apply_record ar on ni.apply_record_id=ar.id");
		sql.append("	and (ar.status=1 or ar.status=4 or ar.status=5) and ni.corp_id=:corpId and ni.status!=4");
		sql.append(" inner join sys_user u on ni.notice_user_id=u.id");
		sql.append(" left join sys_org_user ou on u.id=ou.user_id and ou.isMain='1'");
		sql.append(" left join sys_org o on ou.org_id=o.id");
		sql.append(" where (1=1) ");
		if(StringUtils.isNotBlank(userName)) {
			sql.append(" and u.user_name like :name");
			param.put("name", "%"+ userName +"%");
		}
		if(StringUtils.isNotBlank(orgId)) {
			sql.append(" and ou.org_id=:orgId");
			param.put("orgId", orgId);
		}
		if(StringUtils.isNotBlank(startTime)) {
			sql.append(" and ar.start_time >= :startTime");
			param.put("startTime", startTime + " 00:00");
		}
		if(StringUtils.isNotBlank(endTime)) {
			sql.append(" and ar.start_time <= :endTime");
			param.put("endTime", endTime + "23:59");
		}
		sql.append(" group by ni.notice_user_id,u.user_name,o.org_name order by ni.notice_user_id asc");
		param.put("corpId", corpId);
		List<Map> allList = super.findByListNativeQuery(sql.toString(), "", param);
		List<String> userIdList = new ArrayList<String>();
		if(CollectionUtils.isNotEmpty(allList)) {
			for(Map m : allList) {
				userIdList.add(StringUtils.null2String(m.get("id")));
			}
		}
		if(CollectionUtils.isNotEmpty(userIdList)) {
			Map<String, String> attendNumList = queryAttendMeetingStatistics(userIdList, 1, startTime, endTime);
			Map<String, String> onTimeList = queryAttendMeetingStatistics(userIdList, 2, startTime, endTime);
			Map<String, String> beLateList = queryAttendMeetingStatistics(userIdList, 3, startTime, endTime);
			Map<String, String> leaveList = queryAttendMeetingStatistics(userIdList, 4, startTime, endTime);
			Map<String, String> absentList = queryAttendMeetingStatistics(userIdList, 5, startTime, endTime);
			List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
			if(CollectionUtils.isNotEmpty(allList)) {
				for(Map m : allList) {
					Map<String, Object> element = new HashMap<String, Object>();
					element.put("userName", m.get("user_name"));
					element.put("org", m.get("org_name"));
					String key = StringUtils.null2String(m.get("id"));
					int attendNum = 0, onTime = 0, beLate = 0, leave = 0, absent = 0;
					if(attendNumList.get(key) != null) attendNum = Integer.parseInt(attendNumList.get(key));
					if(onTimeList.get(key) != null) onTime = Integer.parseInt(onTimeList.get(key));
					if(beLateList.get(key) != null) beLate = Integer.parseInt(beLateList.get(key));
					if(leaveList.get(key) != null) leave = Integer.parseInt(leaveList.get(key));
					if(absentList.get(key) != null) absent = Integer.parseInt(absentList.get(key));
					element.put("attendNum", attendNum);
					element.put("onTime", onTime + "(" + getPercent(onTime, attendNum) + "%)");
					element.put("beLate", beLate + "(" + getPercent(beLate, attendNum) + "%)");
					element.put("leave", leave + "(" + getPercent(leave, attendNum) + "%)");
					element.put("absent", absent + "(" + getPercent(absent, attendNum) + "%)");
					list.add(element);
				}
				//List<Map<String, Object>>集合按Map的key排序
//				Collections.sort(list, new Comparator<Map<String, Object>>() {
//					public int compare(Map<String, Object> o1, Map<String, Object> o2) {
//						String map1value = (String) o1.get("userName");
//						String map2value = (String) o2.get("userName");
//						return map2value.compareTo(map1value);
//					}
//				});
			}
	        if(list != null && list.size() > 0){
	            for(int i = 0; i < list.size(); i++){
	            	AttendMeetingStatisticsExport export = new AttendMeetingStatisticsExport();
	                Map<String, Object> map = list.get(i);
	                export.setUserName(StringUtils.null2String(map.get("userName")));
	                export.setOrg(StringUtils.null2String(map.get("org")));
	                export.setAttendNum(StringUtils.null2String(map.get("attendNum")));
	                export.setOnTime(StringUtils.null2String(map.get("onTime")));
	                export.setBeLate(StringUtils.null2String(map.get("beLate")));
	                export.setLeave(StringUtils.null2String(map.get("leave")));
	                export.setAbsent(StringUtils.null2String(map.get("absent")));
	                result.add(export);
	            }
	        }
		}
		return result;
	}
	
	/**
	 * 任务统计导出
	 * @param paramMap
	 * @return
	 */
	public List<TaskStatisticsExport> getTaskStatisticsExport(Map<String, String> paramMap){
		List<TaskStatisticsExport> result = new ArrayList<TaskStatisticsExport>();
		WebUser user = WebUtils.getCurrentUser();
        String corpId = user.getCorpId();
		String userName = paramMap.get("userName");
		String orgId = paramMap.get("orgName");
		String startTime = paramMap.get("startTime");
		String endTime = paramMap.get("endTime");
		Map<String, Object> param = new HashMap<String, Object>();
		StringBuffer sql = new StringBuffer();
		sql.append("select u.user_name, o.org_name, ti.createdBy from oa_meeting_task_info ti,oa_meeting_apply_record_scope s,sys_user u,sys_org_user ou,sys_org o");
		sql.append(" where ti.response_user=s.fieldDataValue and u.id=s.scopeValue and ou.user_id=s.scopeValue and ou.isMain=1");
		sql.append(" and o.id=ou.org_id and ti.corp_id=:corpId");
		if(StringUtils.isNotBlank(userName)) {
			sql.append(" and u.user_name like :name");
			param.put("name", "%"+ userName +"%");
		}
		if(StringUtils.isNotBlank(orgId)) {
			sql.append(" and ou.org_id=:orgId");
			param.put("orgId", orgId);
		}
		if(StringUtils.isNotBlank(startTime)) {
			sql.append(" and fn_date_format(ti.end_time,'yyyy-MM-dd') >= :startTime");
			param.put("startTime", startTime);
		}
		if(StringUtils.isNotBlank(endTime)) {
			sql.append(" and fn_date_format(ti.end_time,'yyyy-MM-dd') <= :endTime");
			param.put("endTime", endTime);
		}
		sql.append(" group by u.user_name, o.org_name, ti.createdBy order by ti.createdBy");
		param.put("corpId", corpId);
		List<Map> allList = super.findByListNativeQuery(sql.toString(), "", param);
		Map<String, Map> completedMap = queryTaskStatistics(1, corpId, userName, orgId, startTime, endTime);
		Map<String, Map> overdueMap = queryTaskStatistics(2, corpId, userName, orgId, startTime, endTime);
		Map<String, Map> unCompletedMap = queryTaskStatistics(3, corpId, userName, orgId, startTime, endTime);
		List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
		if(CollectionUtils.isNotEmpty(allList)) {
			for(Map m : allList) {
				Map<String, Object> element = new HashMap<String, Object>();
				element.put("userName", m.get("user_name"));
				element.put("org", m.get("org_name"));
				String key = StringUtils.null2String(m.get("user_name"));
				int completed = 0, overdue = 0, unCompleted = 0;
				if(completedMap.get(key) != null) completed = Integer.parseInt(completedMap.get(key).get("num").toString());
				if(overdueMap.get(key) != null) overdue = Integer.parseInt(overdueMap.get(key).get("num").toString());
				if(unCompletedMap.get(key) != null) unCompleted = Integer.parseInt(unCompletedMap.get(key).get("num").toString());
				int taskNum = completed + overdue + unCompleted;
				DecimalFormat df=new DecimalFormat("0.00");//设置保留位数
				element.put("taskNum", taskNum);
				element.put("completed", completed + "(" + getPercent(completed, taskNum) + "%)");
				element.put("overdue", overdue + "(" + getPercent(overdue, taskNum) + "%)");
				element.put("unCompleted", unCompleted + "(" + getPercent(unCompleted, taskNum) + "%)");
				list.add(element);
			}
			//List<Map<String, Object>>集合按Map的key排序
//			Collections.sort(list, new Comparator<Map<String, Object>>() {
//				public int compare(Map<String, Object> o1, Map<String, Object> o2) {
//					String map1value = (String) o1.get("userName");
//					String map2value = (String) o2.get("userName");
//					return map2value.compareTo(map1value);
//				}
//			});
		}
        if(list != null && list.size() > 0){
            for(int i = 0; i < list.size(); i++){
            	TaskStatisticsExport export = new TaskStatisticsExport();
                Map<String, Object> map = list.get(i);
                export.setUserName(StringUtils.null2String(map.get("userName")));
                export.setOrg(StringUtils.null2String(map.get("org")));
                export.setTaskNum(StringUtils.null2String(map.get("taskNum")));
                export.setCompleted(StringUtils.null2String(map.get("completed")));
                export.setOverdue(StringUtils.null2String(map.get("overdue")));
                export.setUnCompleted(StringUtils.null2String(map.get("unCompleted")));
                result.add(export);
            }
        }
		return result;
	}
}
